﻿CREATE PROCEDURE [util].[GetJobStatus]
@Name NVARCHAR (128)=NULL, @Id UNIQUEIDENTIFIER=NULL, @Status VARCHAR (30) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @Id = [job_id]
	FROM [msdb]..[sysjobs]
	WHERE ([name] = @Name)
		OR([job_id] = @Id);

	IF(@Id IS NULL)
		BEGIN
			RAISERROR('You must supply a valid value for the @JobName or @JobId parameter',16,1);
			RETURN(1);
		END;

	DECLARE @StatusFlag int
	SET @StatusFlag = 7;
	WHILE(@StatusFlag >= 0)
		BEGIN
			EXEC [msdb].[dbo].[sp_get_composite_job_info] @job_id = @Id, @execution_status = @StatusFlag;
			IF(@@ROWCOUNT <> 0)
				BREAK;
			SET @StatusFlag = @StatusFlag - 1;
		END;

	SET @Status = 
		(CASE @StatusFlag
			WHEN 0 THEN 'Not idle or suspended'
			WHEN 1 THEN 'Executing'
			WHEN 2 THEN 'Waiting For Thread'
			WHEN 3 THEN 'Between Retries'
			WHEN 4 THEN 'Idle'
			WHEN 5 THEN 'Suspended'
			WHEN 6 THEN 'WaitingForStepToFinish'
			WHEN 7 THEN 'PerformingCompletionActions'
		END)

	RETURN(0);
END;